package com.lanyou.esb.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.lanyou.esb.entity.Http;

/**
 * 服务监控
 * 
 * @author Davey.wu
 */
public interface ServiceMonitorRepository extends JpaRepository<Http, String> {
	/**
	 * 查找平台流量，GROUP BY 系统和日期
	 * 
	 * @param fieldIds
	 *            :领域列表
	 * @param startDate
	 *            ：开始日期
	 * @param endDate
	 *            ：结束日期
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT t.FIELD_ID AS fieldId, "
			+ "(SELECT f.FIELD_NAME FROM T_COOK_DB_FIELD f WHERE f.FIELD_ID=t.FIELD_ID) AS fieldName, "
			+ "DATE_FORMAT( t.STATISTICS_DATE, '%Y-%m-%d') AS statDate, " + "t.THROUGHPUT_QTY AS qty  "
			+ "FROM T_ESB_BU_PF_THROUGHPUT t " + "WHERE  "
			+ "t.FIELD_ID IN(:fieldIds) "
			+ "AND t.STATISTICS_DATE >:startDate "
			+ "AND t.STATISTICS_DATE <=:endDate "
			+ "GROUP BY t.FIELD_ID,t.STATISTICS_DATE")
	List<Object[]> finePfThroughputInfoGroupByFieldAndDay(
			@Param("fieldIds") List<String> fieldIds,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	/**
	 * 查找平台流量，GROUP BY 日期
	 * 
	 * @param fieldIds
	 *            :领域列表
	 * @param startDate
	 *            ：开始日期
	 * @param endDate
	 *            ：结束日期
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT "
			+ "DATE_FORMAT( t.STATISTICS_DATE, '%Y-%m-%d') AS statDate, "
			+ "SUM(t.THROUGHPUT_QTY) AS qty "
			+ "FROM T_ESB_BU_PF_THROUGHPUT t " + " WHERE "
			+ "t.STATISTICS_DATE >:startDate  "
			+ "AND t.STATISTICS_DATE <=:endDate  "
			+ "GROUP BY t.STATISTICS_DATE")
	List<Object[]> finePfThroughputInfoGroupByDay(
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	/**
	 * 查找平台流量，GROUP BY 系统和月
	 * 
	 * @param fieldIds
	 *            :领域列表
	 * @param startDate
	 *            ：开始日期
	 * @param endDate
	 *            ：结束日期
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT V1.fieldId AS fieldId, "
			+ "(SELECT f.FIELD_NAME FROM T_COOK_DB_FIELD f WHERE f.FIELD_ID=V1.fieldId) AS fieldName, "
			+ "V1.statDate AS statDate , "
			+ "SUM(V1.qty) AS qty  "
			+ "FROM( "
			+ "	SELECT t.FIELD_ID AS fieldId, "
			+ "	DATE_FORMAT( t.STATISTICS_DATE, '%Y-%m') AS statDate , "
			+ "	t.THROUGHPUT_QTY AS qty  "
			+ "	FROM T_ESB_BU_PF_THROUGHPUT t "
			+ "	WHERE  "
			+ "	t.FIELD_ID IN(:fieldIds) "
			+ "	AND t.STATISTICS_DATE >:startDate  "
			+ "	AND t.STATISTICS_DATE <=:endDate "
			+ ")AS V1 "
			+ "GROUP BY V1.fieldId,V1.statDate ORDER BY V1.fieldId,V1.statDate ASC")
	List<Object[]> finePfThroughputInfoGroupByFieldAndMonth(
			@Param("fieldIds") List<String> fieldIds,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

	/**
	 * 查找平台流量，GROUP BY 月
	 * 
	 * @param fieldIds
	 *            :领域列表
	 * @param startDate
	 *            ：开始日期
	 * @param endDate
	 *            ：结束日期
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT "
			+ "V1.statDate AS statDate , "
			+ "SUM(V1.qty) AS qty  "
			+ "FROM( "
			+ "	SELECT "
			+ "	DATE_FORMAT( t.STATISTICS_DATE, '%Y-%m') AS statDate , "
			+ "	t.THROUGHPUT_QTY AS qty  "
			+ "	FROM T_ESB_BU_PF_THROUGHPUT t "
			+ "	WHERE  "
			+ " t.STATISTICS_DATE >:startDate "
			+ "	AND t.STATISTICS_DATE <=:endDate " + ")AS V1 "
			+ "GROUP BY V1.statDate ORDER BY V1.statDate ASC")
	List<Object[]> finePfThroughputInfoGroupByMonth(
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);
}
